package org.kitten.core.util;

import java.io.BufferedOutputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.OutputStream;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.Region;
import org.kitten.core.util.FileUtil;

/**
 * 写Excel的工具类,只有构造函数和save方法是必须要调用的.用法如下：<br>
 * 
 * <pre>
 * ExcelWriter w = new ExcelWriter();
 * w.addTitle(&quot;hello,这里是标题&quot;, 4);
 * w.addHead(&quot;表头1&quot;, &quot;表头1&quot;, &quot;表头1&quot;, &quot;表头1&quot;);
 * w.addRow(&quot;行1列1&quot;, &quot;行1列2&quot;, &quot;行1列3&quot;, &quot;行1列4&quot;);
 * w.addRow(&quot;行2列1&quot;, &quot;行2列2&quot;, &quot;行2列3&quot;, &quot;行2列4&quot;);
 * //写到文件
 * w.save(new FileOutputStream(&quot;c:/test.xls&quot;));
 * //写到HttpResponse
 * w.save(response);
 * </pre>
 * 
 * @author sb.yin(uniquejava@gmail.com)
 * @version 1.1
 * @since 2010-2-4,最后修改2010-2-21
 */
public class ExcelWriter {
	/**
	 * 工作簿
	 */
	private HSSFWorkbook book;
	/**
	 * 工作单
	 */
	private HSSFSheet sheet;
	/**
	 * 行数
	 */
	private int rowCount;
	/**
	 * 列数
	 */
	private int columnCount;

	public ExcelWriter() {
		this.book = new HSSFWorkbook();
		this.sheet = book.createSheet();
		this.rowCount = 0;
	}

	/**
	 * 
	 * @param sheetName
	 *            Excel底部的sheet(表单)名称
	 */
	public ExcelWriter(String sheetName) {
		this.book = new HSSFWorkbook();
		this.sheet = book.createSheet(sheetName);
		this.rowCount = 0;
	}

	/**
	 * 添加标题栏.
	 * 
	 * @param title
	 * @param columnCount
	 */
	public void addTitle(String title, int columnCount) {
		if (this.columnCount == 0) {
			this.columnCount = columnCount;
		}
		HSSFRow row = sheet.createRow(rowCount);
		HSSFCell cell = row.createCell((short) 0);
		HSSFFont cellFont = book.createFont();
		// cellFont.setColor(HSSFColor.RED.index);
		cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellFont.setFontName("宋体");
		cellFont.setFontHeightInPoints((short) 12);
		HSSFCellStyle cellStyle = book.createCellStyle();
		cellStyle.setFont(cellFont);
		cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		cell.setCellStyle(cellStyle);
		cell.setCellValue(title);
		Region re = new Region(0, (short) 0, 0, (short) (columnCount - 1));
		sheet.addMergedRegion(re);
		rowCount++;
	}

	/**
	 * 添加表头
	 * 
	 * @param values
	 */
	public void addHead(String... values) {
		if (columnCount == 0) {
			columnCount = values.length;
		}
		HSSFRow row = sheet.createRow(rowCount);
		for (int i = 0; i < values.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			HSSFFont cellFont = book.createFont();
			// cellFont.setColor(HSSFColor.BLACK.index);
			cellFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			cellFont.setFontName("宋体");
			HSSFCellStyle cellStyle = book.createCellStyle();
			cellStyle.setFont(cellFont);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			cell.setCellStyle(cellStyle);
			cell.setCellValue(values[i]);
		}
		rowCount++;
	}

	public void addMerge(int a, int b, int c, int d) {
		Region re = new Region((short) a, (short) b, (short) c, (short) d);
		sheet.addMergedRegion(re);
	}

	public void addColSpan(int x, int length) {
		short columnNum = (short) (rowCount - 1);
		short to = (short) (x + length-1);
		Region re = new Region(columnNum, (short) x, columnNum, to);
		sheet.addMergedRegion(re);
	}

	public void addRowSpan(int y, int length) {
		short columnNum = (short) (rowCount - 1);
		short to = (short) (columnNum + length-1);
		Region re = new Region(columnNum, (short) y, to, (short) y);
		sheet.addMergedRegion(re);
	}

	/**
	 * 添加一行数据.
	 * 
	 * @param values
	 */
	public void addRow(String... values) {
		HSSFRow row = sheet.createRow(rowCount);
		for (int i = 0; i < values.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			cell.setCellValue(values[i]);
		}
		rowCount++;
	}

	public void removeLastRow() {
		if (rowCount > 0) {
			sheet.removeRow(sheet.getRow(rowCount - 1));
			rowCount--;
		}
	}

	/**
	 * 写到输出流.
	 * 
	 * @param os
	 * @throws Exception
	 */
	public void save(OutputStream os) throws Exception {
		save(os, true);
	}

	public void save(OutputStream os, boolean autoSizeColumn) throws Exception {
		if (autoSizeColumn) {
			for (short k = 0; k < columnCount; k++) {
				sheet.autoSizeColumn(k);
			}
		}
		try {
			book.write(os);
			os.flush();
			os.close();
		} finally {
			FileUtil.close(os);
		}
	}

	/**
	 * 写到HttpResponse.
	 * 
	 * @param response
	 * @throws Exception
	 */
	public void save(HttpServletResponse response) throws Exception {
		save(response, DateUtil.getFormatedDate("yyyyMMddHHmmss") + ".xls",
				false);
	}

	public void save(HttpServletResponse response, boolean resetResponse)
			throws Exception {
		save(response, DateUtil.getFormatedDate("yyyyMMddHHmmss") + ".xls",
				resetResponse);
	}

	public void save(HttpServletResponse response, String fileName,
			boolean resetResponse) throws Exception {
		ByteArrayOutputStream baos = null;
		BufferedOutputStream bos = null;
		ServletOutputStream sos = null;
		try {
			for (short k = 0; k < columnCount; k++) {
				sheet.autoSizeColumn(k);
			}

			baos = new ByteArrayOutputStream();
			bos = new BufferedOutputStream(baos);
			book.write(bos);
			bos.flush();
			if (resetResponse) {
				response.reset();// java.net.ProtocolException: Exceeded
				// stated content-length of: 错误，解决办法
			}
			response.addHeader("content-type", "application/x-msdownload;");
			response.addHeader("Content-Disposition", "attachment;filename="
					+ fileName);
			response.setContentLength(baos.size());
			sos = response.getOutputStream();
			baos.writeTo(sos);
			sos.flush();
		} catch (Exception e) {
			throw e;
		} finally {
			//System.out.println("FileUtil.close(baos);;");
			FileUtil.close(baos);
		}
	}

	public int getColumnCount() {
		return columnCount;
	}

	public void setColumnCount(int columnCount) {
		this.columnCount = columnCount;
	}

	public static void main(String[] args) throws Exception {
		ExcelWriter w = new ExcelWriter();
		w.addTitle("hello,这里是标题", 4);
		w.addHead("表头1", "表头2", "表头3", "表头4");
		w.addRow("行1列1", "行1列2", "行1列3", "行1列4");
		w.addRow("行2列1", "行2列2", "行2列3", "行2列4");
		w.addRowSpan(1, 2);
		// 写到文件
		w.save(new FileOutputStream("c:/test1.xls"));
	}
}
